
package com.compuvest.tree.upload

import groovy.sql.Sql

import javax.naming.InitialContext

class SQLService {

    def sql

    def SQLService() {

        def ds = new InitialContext().lookup("jdbc/mysql")
        sql = new Sql(ds)

        sql.eachRow('select VERSION()') { row ->
            println '>>>>UploadJSON SQLService() version=' << row[0]
        }
        sql.close()
    }

// <editor-fold defaultstate="collapsed" desc="updateCategories()">
    def static final UPDATE_CATEGORIES = "{ call sklad.doCategories(?,?,?,?,?,?) }" //accwareParent,accwareCode,name,active

    def updateCategories(accwareParent, accwareCode, name, etilizeid, type, active) { //type C-category, P-preference, A-AttributeName

        sql.call(UPDATE_CATEGORIES, [
            accwareParent,
            accwareCode,
            name,
            etilizeid,
            type,
            active
        ])
        sql.close()
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getAllCategories()">
//SELECT IFNULL(GROUP_CONCAT(CONCAT('{id:',id,',name:',QUOTE(name),'}')),'') json FROM sklad.categories WHERE active='N';
    def getCategory(active, parentId) {

        def json = ''
        //njson.push({property: { name: row.n }, data: { id: row.id, pid: row.pid, cid: row.cid, copy:row.c, type:row.t, active: row.a }});
        sql.eachRow("""
            SELECT IFNULL(CAST(GROUP_CONCAT(CONCAT(
            '{"property":{"name":"',name,'"},',
            '"data":{',
            '"id":',id,
            ',"pid":',parent_id,
            ',"cid":',category_id,
            ',"copy":',copy,
            ',"type":"',type,
            '","active":"',active,
            '"}}')) AS CHAR),'') json 
            FROM sklad.categories 
            WHERE active=? AND parent_id=?""", [active, parentId])
        { row ->
            json = "[${row.json}]"
        }
        sql.close()

        return json
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="moveNodePanel()">
    def moveNodePanel(id, pid, ids, active) {

        //println "ids=${ids} ids=${ids.split(":")}"
        sql.executeUpdate("UPDATE sklad.categories SET active=?,parent_id=? WHERE id=?", [active, pid, id])
        sql.close()

//        sql.executeUpdate("UPDATE sklad.categories SET active=1 WHERE id IN (${ids})")
//        sql.close()
        if(ids) { // not empty
            sql.withBatch("UPDATE sklad.categories SET active=? WHERE id=?") { row ->
                ids.split(":").each { nodeid ->
                    row.addBatch(active, nodeid as Integer)
                }
            }
            sql.close()
        }

        return """{ "id": 0 }"""
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="copyNodePanel()">
    def copyNodePanel(id, pid, type, active) {

        def category_id = 0, name = ''
        sql.eachRow("SELECT category_id,name FROM sklad.categories WHERE id=?", [id])
        { row ->
            category_id = row.category_id
            name = row.name
        }
        sql.close()

        def newid = sql.executeInsert("INSERT INTO sklad.categories SET copy=1,parent_id=?,category_id=?,name=?,type=?,active=?",
                                      [pid,category_id,name,type,active]).get(0).get(0) as Integer
        sql.close()

        return """{ "id": ${newid}, "pid": ${pid}, "cid": ${category_id}, "copy": 1, "type": "${type}", "active": "${active}" }"""
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="renameCategory()">
    def renameCategory(id, name) {

        sql.executeUpdate("UPDATE sklad.categories SET name=? WHERE id=?", [name, id])
        sql.close()
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="insertCategory()">

    def insertCategory(cid, name, active, where, type) {

        def id = sql.executeInsert("INSERT INTO sklad.categories SET name=?,parent_id=?,type=?", [name, cid, type]).get(0).get(0) as Integer
        sql.close()

        sql.executeUpdate("UPDATE sklad.categories SET category_id=?,active=? WHERE id=?", [id, active, id])
        sql.close()

        return """{ "id": ${id}, "pid": ${cid}, "cid": ${id}, "copy": 0, "active": "${active}", "type":"${type}" }"""
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="removeCategory()">
    def removeCategory(ids) {

        if(ids) { // not empty
            sql.withBatch("UPDATE sklad.categories SET active='D' WHERE id=?") { row ->
                ids.split(":").each { nodeid ->
                    row.addBatch(nodeid as Integer)
                }
            }
            sql.close()
        }
    }
// </editor-fold>

}
